﻿update entities
set state = 2
from entities e
where not exists (
select *
from Entities ee
inner join EntityFBTypes ft on ft.EntityID = ee.EntityID
inner join FreebaseTypes t on t.id = ft.FBTypeID and (t.Name 
	in (
		'/book/book',
		'/people/person',
		'/location/location',
		'/music/album',
		'/organization/organization',
		'/book/author',
		'/music/artist',
		'/fictional_universe/fictional_character',
		'/business/business_operation',
		'/sports/pro_athlete',
		'/film/film_character',
		'/film/actor',
		'/film/film',
		'/education/educational_institution',
		'/music/musical_group',
		'/education/school',
		'/music/group_member',
		'/soccer/football_player',
		'/architecture/structure',
		'/tv/tv_character',
		'/business/business_location',
		'/olympics/olympic_athlete',
		'/architecture/building',
		'/tv/tv_program',
		'/award/award_nominated_work',
		'/cvg/computer_videogame',
		'/education/academic',
		'/visual_art/artwork',
		'/sports/sports_team',
		'/education/educational_institution_campus',
		'/visual_art/visual_artist',
		'/education/university',
		'/boats/ship',
		'/fictional_universe/work_of_fiction',
		'/royalty/noble_person',
		'/soccer/football_team',
		'/aviation/airport',
		'/library/public_library',
		'/book/book_character',
		'/business/brand',
		'/computer/software',
		'/government/political_district',
		'/government/political_party',
		'/architecture/museum',
		'/book/newspaper',
		'/medicine/drug',
		'/aviation/aircraft_model',
		'/book/magazine',
		'/internet/website',
		'/wine/wine',
		'/transportation/bridge',
		'/people/ethnicity',
		'/projects/project',
		'/cvg/cvg_publisher',
		'/book/journal',
		'/theater/play',
		'/medicine/hospital',
		'/dining/restaurant',
		'/automotive/model',
		'/business/defunct_company',
		'/exhibitions/exhibition',
		'/sports/boxer',
		'/film/production_company',
		'/aviation/airline',
		'/architecture/skyscraper',
		'/organization/non_profit_organization',
		'/food/beer',
		'/food/food',
		'/royalty/monarch',
		'/business/shopping_center',
		'/religion/religious_leader',
		'/boats/ship_class',
		'/education/field_of_study',
		'/broadcast/tv_station',
		'/games/game',
		'/celebrities/celebrity',
		'/travel/hotel',
		'/tv/tv_network',
		'/cvg/game_character',
		'/music/instrument',
		'/government/governmental_body',
		'/rail/railway',
		'/ice_hockey/hockey_team',
		'/computer/software_developer',
		'/fictional_universe/fictional_organization',
		'/religion/deity',
		'/digicams/digital_camera',
		'/wine/wine_producer',
		'/theater/theater',
		'/computer/programming_language',
		'/sports/professional_sports_team',
		'/zoos/zoo_animal',
		'/spaceflight/spacecraft',
		'/language/language_family',
		'/automotive/body_style',
		'/internet/blog',
		'/engineering/engine',
		'/games/game_expansion',
		'/computer/computer',
		'/religion/religion',
		'/biology/hybrid',
		'/sports/sport',
		'/music/orchestra',
		'/american_football/football_team',
		'/finance/stock_exchange',
		'/fictional_universe/fictional_universe',
		'/automotive/company',
		'/soccer/football_league',
		'/conferences/conference',
		'/religion/monastery',
		'/royalty/order_of_chivalry',
		'/religion/religious_text',
		'/computer/computer_processor',
		'/government/government',
		'/education/student_organization',
		'/bicycles/bicycle_model',
		'/music/concert',
		'/computer/software_license',
		'/spaceflight/rocket',
		'/religion/belief',
		'/aviation/aircraft'
	)
	or t.name like '/astronomy%'
	or t.name like '/geography%'
	or t.name like '/opera%'
	or t.name like '/computer%'
	or t.name like '/games%'
	or t.name like '/spaceflight%'
	or t.name like '/comic_books%'
	or t.name like '/aviation%'
	or t.name like '/cvg%')
where ee.EntityID = e.EntityID
)

select *
from FreebaseTypes

select '''' + t.Name + ''',' , count(*)
from EntityFBTypes ft
inner join FreebaseTypes t on t.id = ft.FBTypeID
group by t.Name
order by count(*) desc

delete from FreebaseTypes
where name like '/m/%'